﻿using Marbles.Data;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Marbles.CMS.ContentTypes.Tasks
{
    public class ContentManager : ContentManagerBase
    {

        public Dictionary<int, string> GetTaskStates()
        {
            Dictionary<int, string> items = new Dictionary<int, string>();
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("SELECT * FROM TaskStatus");
            DataTable dt = dbc.FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                items.Add(Convert.ToInt32(dr["taskstatusid"].ToString()), dr["title"].ToString());
            }

            return items;
        }

        public Dictionary<int, string> GetTaskPriorities()
        {
            Dictionary<int, string> items = new Dictionary<int, string>();
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("SELECT * FROM TaskPriority");
            DataTable dt = dbc.FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                items.Add(Convert.ToInt32(dr["taskpriorityid"].ToString()), dr["title"].ToString());
            }

            return items;
        }

        public void SaveTask(TaskEntryItem value)
        {
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = null;

            if (value.TaskId <= 0)
            {
                cmd = new DataSourceCommand("DECLARE @taskid int;SET @taskid = 0;SELECT TOP 1 @taskid = taskid FROM Task WHERE username = @username ORDER BY taskid DESC; SET @taskid = @taskid + 1;INSERT INTO Task (taskid, username, subject, taskstatusid, taskpriorityid, notes, duedate, startdate, completiondate) VALUES (@taskid, @username, @subject, @taskstatusid, @taskpriorityid, @notes, @duedate, @startdate, @completiondate)");
            }
            else
            {
                cmd = new DataSourceCommand("UPDATE Task SET subject = @subject, taskstatusid = @taskstatusid, taskpriorityid = @taskpriorityid, notes = @notes, duedate = @duedate, startdate = @startdate, completiondate = @completiondate WHERE taskid = @taskid AND username = @username");
                cmd.Parameters.Add("@taskid", value.TaskId);
            }

            if (value.CompletionDate == DateTime.MaxValue)
            {
                cmd.Parameters.Add("@completiondate", null);
            }
            else
            {
                cmd.Parameters.Add("@completiondate", value.CompletionDate);
            }

            cmd.Parameters.Add("@username", value.Username);
            cmd.Parameters.Add("@subject", value.Subject);
            cmd.Parameters.Add("@taskstatusid", value.TaskStatusId);
            cmd.Parameters.Add("@taskpriorityid", value.TaskPriorityId);
            cmd.Parameters.Add("@notes", value.Notes);
            cmd.Parameters.Add("@duedate", value.DueDate);
            cmd.Parameters.Add("@startdate", value.StartDate);
            dbc.ExecuteCommand(cmd);
        }

        public TaskEntryItem GetTaskEntry(int taskid, string username)
        {
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("SELECT a.*, b.title AS taskstatus, c.title AS taskpriority FROM Task a INNER JOIN TaskStatus b ON a.taskstatusid = b.taskstatusid INNER JOIN TaskPriority c ON a.taskpriorityid = c.taskpriorityid WHERE a.username = @username AND a.taskid = @taskid");
            cmd.Parameters.Add("@username", username);
            cmd.Parameters.Add("@taskid", taskid);
            DataTable dt = dbc.FetchDataTable(cmd);
            TaskEntryItem item = null;

            if (dt.Rows.Count > 0)
            {
                item = new TaskEntryItem(Convert.ToInt32(dt.Rows[0]["taskid"].ToString()), dt.Rows[0]["username"].ToString());
                item.DueDate = Convert.ToDateTime(dt.Rows[0]["duedate"]);
                item.Notes = dt.Rows[0]["notes"].ToString();
                item.StartDate = Convert.ToDateTime(dt.Rows[0]["startdate"]);
                item.Subject = dt.Rows[0]["subject"].ToString();
                item.TaskPriorityId = Convert.ToInt32(dt.Rows[0]["taskpriorityid"].ToString());
                item.TaskStatusId = Convert.ToInt32(dt.Rows[0]["taskstatusid"].ToString());
                if (dt.Rows[0]["completiondate"] != DBNull.Value) { item.CompletionDate = Convert.ToDateTime(dt.Rows[0]["completiondate"]); }

            }

            return item;
        }

        public List<TaskEntryItem> GetTasks(string username, bool ExcludeCompleted)
        {
            List<TaskEntryItem> items = new List<TaskEntryItem>();
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("SELECT a.*, b.title AS taskstatus, c.title AS taskpriority FROM Task a INNER JOIN TaskStatus b ON a.taskstatusid = b.taskstatusid INNER JOIN TaskPriority c ON a.taskpriorityid = c.taskpriorityid WHERE a.username = @username AND (a.taskstatusid <> @taskstatusid OR @taskstatusid = 0) ORDER BY a.duedate DESC");
            cmd.Parameters.Add("@username", username);

            if (ExcludeCompleted)
            {
                cmd.Parameters.Add("@taskstatusid", 2);
            }
            else
            {
                cmd.Parameters.Add("@taskstatusid", 0);
            }

            DataTable dt = dbc.FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                TaskEntryItem item = new TaskEntryItem(Convert.ToInt32(dr["taskid"].ToString()), dr["username"].ToString());
                item.DueDate = Convert.ToDateTime(dr["duedate"]);
                item.Notes = dr["notes"].ToString();
                item.StartDate = Convert.ToDateTime(dr["startdate"]);
                item.Subject = dr["subject"].ToString();
                item.TaskPriorityId = Convert.ToInt32(dr["taskpriorityid"].ToString());
                item.TaskStatusId = Convert.ToInt32(dr["taskstatusid"].ToString());
                if (dr["completiondate"] != DBNull.Value) { item.CompletionDate = Convert.ToDateTime(dr["completiondate"]); }

                items.Add(item);
            }

            return items;
        }

        public void CancelTask(int Taskid, string Username)
        {
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("DELETE FROM Task WHERE taskid = @taskid AND username = @username");
            cmd.Parameters.Add("@username", Username);
            cmd.Parameters.Add("@taskid", Taskid);
            dbc.ExecuteCommand(cmd);
        }

        public void CompleteTask(int Taskid, string Username)
        {
            DataSource dbc = new DataSource(this.ContentSettings.connectionString);
            DataSourceCommand cmd = new DataSourceCommand("UPDATE Task SET completiondate = getdate(), taskstatusid = 2 WHERE taskid = @taskid AND username = @username");
            cmd.Parameters.Add("@username", Username);
            cmd.Parameters.Add("@taskid", Taskid);
            dbc.ExecuteCommand(cmd);
        }
    }
}
